import pymysql

db = pymysql.connect( "10.2.5.100", "root", "Kc@123456", "helmet_operate_sit" )
cursor = db.cursor()
def update_length():
    get_length_sql = "SELECT LENGTH(fault_number), fault_number,id FROM kc_device_quality_fault_t"
    cursor.execute(get_length_sql)
    fetchall = cursor.fetchall()
    map_id = {}
    for size in fetchall:
        if size[0] == 24:
           number = size[1]
           map_id[size[2]] = number[0:20]

    for id in map_id:
        number_ = map_id.get( id )
        update_length_sql = 'update kc_device_quality_fault_t set fault_number = \'{0}\' where id = \'{1}\''.format(number_,id)
        cursor.execute(update_length_sql)
        db.commit()


def update_user_id():
    sql = 'SELECT verify_user,id FROM kc_verify_dev_info_t'
    cursor.execute( sql )
    fetchall = cursor.fetchall()
    for user in fetchall:
        if len( str( user[0] ) ) < 5:
            getUserSql = 'SELECT user_id  FROM sys_user  WHERE user_realname = \'{0}\''.format( user[0] )
            cursor.execute( getUserSql )
            fetchone_ = cursor.fetchone()
            if fetchone_ is not None:
                updatesql = 'update kc_verify_dev_info_t set verify_user = \'{0}\''.format( fetchone_[0] )
                print( updatesql )
                cursor.execute( updatesql )
                db.commit()
            else:
                updatesql = 'update kc_verify_dev_info_t set verify_user = \'{0}\''.format(
                    'eea529b7-0e8a-11eb-bf46-0242ac110006' )
                cursor.execute( updatesql )
                print( updatesql )
                db.commit()


if __name__ == '__main__':
    update_length()
